﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'fn_GetAllChildTiers'))
DROP FUNCTION [fn_GetAllChildTiers]
GO
CREATE FUNCTION [fn_GetAllChildTiers](
	@TargetTierKey [uniqueidentifier]
)
RETURNS @ReturnValue TABLE
(
	[ChildTierKey] UNIQUEIDENTIFIER
)

AS
BEGIN
	
	WITH [tmp] AS  
	(
		SELECT [key] FROM GeographyTier  WHERE [Key]=@TargetTierKey
		UNION  ALL
		SELECT a.[key] FROM GeographyTier a 
		JOIN [tmp] b ON a.[parentKey]=b.[Key]
	)
	INSERT INTO @ReturnValue([ChildTierKey])SELECT [Key] FROM [tmp] WHERE [Key]<>@TargetTierKey;
			
	RETURN;	
END
GO